package cong.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dy.nygl.pojo.ElecStatus;
import com.dy.nygl.pojo.Energy;
import com.dy.nygl.pojo.EnergyYear;
import com.dy.nygl.pojo.FromTo;

public class JdbcConn {

	String driver = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://localhost:3306/nygl";
	String username = "root";
	String password = "cong";
	Connection con;
	Statement st;
	ResultSet rs;

	public static void main(String[] args) {
		new JdbcConn().getElecStatus();

	}

	public List<EnergyYear> getYearList() {

		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);//
			st = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

		List<EnergyYear> yearList = new ArrayList<>();
		String sql = "select * from energy_year";
		try {
			rs = st.executeQuery(sql);
			while (rs.next()) {
				EnergyYear energyYear = new EnergyYear();
				energyYear.setId(rs.getInt("id"));
				energyYear.setName(rs.getString("name"));
				energyYear.setJan(rs.getString("jan"));
				energyYear.setFeb(rs.getString("feb"));
				energyYear.setMar(rs.getString("mar"));
				energyYear.setApr(rs.getString("apr"));
				energyYear.setMay(rs.getString("may"));
				energyYear.setJun(rs.getString("jun"));
				energyYear.setJuly(rs.getString("july"));
				energyYear.setAug(rs.getString("aug"));
				energyYear.setSep(rs.getString("sep"));
				energyYear.setOct(rs.getString("oct"));
				energyYear.setNov(rs.getString("nov"));
				energyYear.setDec1(rs.getString("dec1"));
				energyYear.setYear(rs.getString("year"));
				yearList.add(energyYear);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return yearList;
	}

	public List<ElecStatus> getElecStatus() {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);//
			st = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

		List<ElecStatus> elecList = new ArrayList<>();
		String sql = "select * from elecstatus";
		try {
			rs = st.executeQuery(sql);
			while (rs.next()) {
				ElecStatus elecStatus = new ElecStatus();
				elecStatus.setId(rs.getInt("id"));
				elecStatus.setName(rs.getString("name"));
				elecStatus.setStatus(rs.getString("status"));
				elecStatus.setData(rs.getString("data"));
				elecList.add(elecStatus);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return elecList;
	}

	public List<Energy> getList() {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);//
			st = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}

		List<Energy> enerylist = new ArrayList<>();
		String sql = "select * from energy";
		try {
			rs = st.executeQuery(sql);
			while (rs.next()) {
				Energy energy = new Energy();
				energy.setId(rs.getInt("id"));
				energy.setName(rs.getString("name"));
				energy.setActual(rs.getString("actual"));
				enerylist.add(energy);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return enerylist;
	}

	public FromTo getfrom() {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);//
			st = con.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		List<FromTo> list = new ArrayList<>();

		String sql = "select * from fromto";
		try {
			rs = st.executeQuery(sql);
			while (rs.next()) {

				FromTo fromTo = new FromTo();
				fromTo.setId(rs.getInt("id"));
				fromTo.setFromgo(rs.getString("fromgo"));
				fromTo.setTogo(rs.getString("togo"));
				fromTo.setCreatetime(rs.getString("createTime"));
				list.add(fromTo);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		FromTo fromTo2 = list.get(0);
		return fromTo2;

	}
}
